package databaseproject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

/**
 *
 * @author jesseday
 */
public class Album {
    private static Connection con;
    private static String username;
    private static boolean userIsAdmin;

    
    public static void doMenu(Connection nCon, String nUsername, boolean nUserIsAdmin)
    {
        con = nCon;
        username = nUsername;
        userIsAdmin = nUserIsAdmin;
        
        boolean done = false;

        while (!done)
        {
            Menu menu = new Menu();
            menu.setTitle("Albums");
            menu.addOption("List Albums");
            if (userIsAdmin)
            {
                menu.addOption("Add Album");
                menu.addOption("Add songs to album");
                
            }
            switch (menu.display())
            {
                case (1):
                    Menu listMenu = new Menu();
                    listMenu.addOptionsFromSQLQuery(con, "select title from ALBUM");
                    int choice = listMenu.display();
                    displayDetail(listMenu.getOptionAt(choice));
                    break;
                
                case (2):
                    if (!userIsAdmin)
                    {
                        done = true;                        
                        break;       
                    }
                    Scanner input = new Scanner(System.in);
                    
                    System.out.print("Select a Band: ");
                    Menu newMenu = new Menu();
                    newMenu.removeBackOption();
                    newMenu.addOptionsFromSQLQuery(con, "select bname from BAND");
                    int bandChoice = newMenu.display();
                    String band = newMenu.getOptionAt(bandChoice);

                    System.out.print("Album Title: ");
                    String title = input.nextLine();
                    System.out.print("Release Year: ");
                    int year = input.nextInt();
                    System.out.print("Record Label: ");
                    String label = input.next();
                    
                    
                    try
                    {
                        PreparedStatement st = con.prepareStatement("insert into ALBUM values (?,?,?,?)");
                        st.setString(1, band);
                        st.setString(2,title);
                        st.setInt(3, year);
                        st.setString(4, label);
                        st.executeUpdate();
                    }
                    catch (java.sql.SQLException e)
                    {
                        System.out.println("SQL failed in add band." + e);
                        
                    }
                    break;
                case (3) :
                    if (!userIsAdmin)
                    {
                        done = true;                        
                        break;       
                    }
                    Scanner read = new Scanner(System.in);
                    System.out.print("Select an Album: ");
                    Menu addSongMenu = new Menu();
                    addSongMenu.removeBackOption();
                    addSongMenu.addOptionsFromSQLQuery(con, "select title from ALBUM");
                    int albumChoice = addSongMenu.display();
                    String album = addSongMenu.getOptionAt(albumChoice);
                    
                    
                    System.out.print("Select a Song: ");
                    Menu selectSongMenu = new Menu();
                    selectSongMenu.removeBackOption();
                    selectSongMenu.addOptionsFromSQLQuery(con, "select sname from SONG");
                    int songChoice = selectSongMenu.display();
                    String songTitle = addSongMenu.getOptionAt(albumChoice);
                    
                    
                    try{
                    PreparedStatement pStmt = con.prepareStatement("select bname from ALBUM where title=?",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
                    pStmt.setString(1, album);
                    ResultSet rs = pStmt.executeQuery();
                    rs.first();
                    PreparedStatement st = con.prepareStatement("insert into ALBUM_SONG values (?,?,?)");
                        st.setString(1, songTitle);
                        st.setString(2,rs.getString("bname"));
                        st.setString(3, album);
                        st.executeUpdate();
                    
                    }
                    catch(java.sql.SQLException e){
                        
                    }
                    break;
                
                default:
                    done = true;
                    break;
            }
        }
    }
    
    public static void displayDetail(String selection)
    {
        try
        {
            PreparedStatement stmt = con.prepareStatement("select * from ALBUM where title=?",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt.setString(1, selection);
            ResultSet rs = stmt.executeQuery();


            rs.first();
            
            System.out.printf("Band Name: %s\n",rs.getString("bname"));
            System.out.printf("Album Title: %s\n",rs.getString("title"));
            System.out.printf("Year of Release: %s\n",rs.getInt("release_yr"));
            System.out.printf("Record Label: %s\n",rs.getString("label"));
            
            stmt = con.prepareStatement("select ALBUM_SONG.sname from SONG join ALBUM_SONG "
                    + "on ALBUM_SONG.sname=SONG.sname where title=?",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt.setString(1, selection);
            rs = stmt.executeQuery();
            
            rs.beforeFirst();
            int i = 1;
            while (rs.next())
            {
                if (i == 1)
                    System.out.println("Songs on album:");
                
                System.out.printf("  %2d: %s\n", i++, rs.getString("sname"));
               
            }
            

        }
        catch (java.sql.SQLException e)
        {
            return;
        }
        
    }
}
